package persistence.db;

import gui.common.SizeUnits;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import model.entity.Product;
import model.entity.container.ProductContainer;
import model.entity.container.ProductGroup;
import model.entity.container.StorageUnit;
import model.exceptions.InvalidNameException;
import model.exceptions.NullContainerException;
import persistence.ProductsDAO;
import common.Barcode;
import common.Result;
import common.Size;
import common.ValueResult;

/**
 * @author Trevor Dixon
 * 
 *         Contains methods for retrieving, adding, updating, and deleting products from a SQL data
 *         store.
 */
public class SQLProductsDAO implements ProductsDAO
{
	private SQLDatabase db;

	public SQLProductsDAO(SQLDatabase db)
	{
		this.db = db;
	}

	@Override
	public ValueResult<Set<Product>> getAll()
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Set<Product> products = new HashSet<Product>();

		try
		{
			String query = "SELECT description, barcode, size_measure, "
					+ "size_unit, shelf_life, three_month_supply FROM products";
			stmt = db.getConnection().prepareStatement(query);

			rs = stmt.executeQuery();
			while (rs.next())
			{
				String description = rs.getString(1);
				Barcode barcode = new Barcode(rs.getString(2));
				float size_measure = rs.getFloat(3);
				String size_unitString = rs.getString(4);
				String Bigletter = size_unitString.substring(0, 1);
				String theRest = size_unitString.substring(1);
				SizeUnits size_unit = SizeUnits.valueOf(Bigletter.toUpperCase() + theRest);
				int shelf_life = rs.getInt(5);
				int three_month_supply = rs.getInt(6);
				Size size = new Size(size_measure, size_unit);
                List<Integer> contianerIds = getContainerIdsByProductBarcode(rs.getString(2)).getValue();
				Product newProduct = new Product(barcode, description, size, shelf_life,
						three_month_supply);
				newProduct.setProductContainerIds(contianerIds);
				products.add(newProduct);
			}
		
		}
		catch (SQLException e)
		{
			System.out.println("Error in getAll(products)");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				System.out.println("Error in execution");
				e.printStackTrace();
			}
		}
		ValueResult<Set<Product>> result = new ValueResult<Set<Product>>(true);
		result.setValue(products);
		return result;
	}
	
	private ValueResult<List<Integer>> getContainerIdsByProductBarcode(String barcode){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Integer> containerIds = new ArrayList<Integer>();

		try
		{
			String query = "SELECT container_id FROM products_to_containers WHERE barcode = ?";
			stmt = db.getConnection().prepareStatement(query);
			stmt.setString(1, barcode);

			rs = stmt.executeQuery();
			while (rs.next())
			{
				containerIds.add(rs.getInt(1));
			}
		
		}
		catch (SQLException e)
		{
			System.out.println("Error in getAll(products)");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				System.out.println("Error in execution");
				e.printStackTrace();
			}
		}
		ValueResult<List<Integer>> result = new ValueResult<List<Integer>>(true);
		result.setValue(containerIds);
		return result;	
	}

	@Override
	public ValueResult<Map<ProductContainer, Set<Product>>> getProductsByContainer()
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Product resultProduct = null;

		Map<ProductContainer, Set<Product>> productsByContainer =
				new HashMap<ProductContainer, Set<Product>>();

		Map<Integer, ProductContainer> containers = new HashMap<Integer, ProductContainer>();

		try
		{
			String query = "SELECT * FROM products_to_containers AS C JOIN products AS P "
					+ "ON C.barcode=P.barcode JOIN product_containers AS PC ON C.container_id=PC.id;";
			stmt = db.getConnection().prepareStatement(query);
			rs = stmt.executeQuery();

			while (rs.next())
			{
				Barcode barcode = new Barcode(rs.getString(1));
				String description = rs.getString(3);
				float size_measure = rs.getFloat(5);
				String size_unit = rs.getString(6);
				int shelf_life = rs.getInt(8);
				int three_month_supply = rs.getInt(9);
				String Bigletter = size_unit.substring(0, 1);
				String theRest = size_unit.substring(1);
				Size size = new Size(size_measure, SizeUnits.valueOf(Bigletter.toUpperCase() + theRest));
				resultProduct = new Product(barcode, description, size, shelf_life, three_month_supply);

				int id = rs.getInt(9);
				int container_parent_id = rs.getInt(10);
				String name = rs.getString(11);
				float three_month_measure = rs.getFloat(12);
				String size_unit2;
				String Bigletter2;
				String theRest2;
				SizeUnits three_month_unit;
				if(rs.getString(13) != null)
				{
					size_unit2 = rs.getString(13);
					Bigletter2 = size_unit2.substring(0, 1);
					theRest2 = size_unit2.substring(1);
					three_month_unit = SizeUnits.valueOf(Bigletter2.toUpperCase() + theRest2);
				}
				else
				{
					three_month_unit = null;
				}
				
				if (!containers.containsKey(id))
				{
					ProductContainer container = (container_parent_id > 0) ? new StorageUnit(name)
					: new ProductGroup(name, null, new Size(three_month_measure, three_month_unit));
					containers.put(id, container);
					productsByContainer.put(container, new HashSet<Product>());
				}

				ProductContainer container = containers.get(id);
				productsByContainer.get(container).add(resultProduct);

			}
		}
		catch (SQLException e)
		{
			return new ValueResult<Map<ProductContainer, Set<Product>>>(false, "SQLException");
		}
		catch (InvalidNameException e)
		{
			return new ValueResult<Map<ProductContainer, Set<Product>>>(false, "Invalid Name");
		}
		catch (NullContainerException e)
		{
			return new ValueResult<Map<ProductContainer, Set<Product>>>(false, "Null Container");
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					rs.close();
			}
			catch (SQLException e)
			{
				System.out.println("Execution error");
				e.printStackTrace();
			}
		}
		ValueResult<Map<ProductContainer, Set<Product>>> result =
				new ValueResult<Map<ProductContainer, Set<Product>>>(true);
		result.setValue(productsByContainer);
		return result;
	}

	public ValueResult<Product> getProductByBarcode(String barcode)
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Product resultProduct = null;

		try
		{
			String query = "SELECT description, barcode, size_measure, size_unit, shelf_life, "
					+ "three_month_supply FROM products WHERE barcode = ?";
			stmt = db.getConnection().prepareStatement(query);
			stmt.setString(1, barcode);

			rs = stmt.executeQuery();
			if (rs.next())
			{
				Barcode tBarcode = new Barcode(rs.getString(2));
				String tDescription = rs.getString(1);
				float tSize = rs.getFloat(3);
				String tUnit = rs.getString(4);
				int tShelf_life = rs.getInt(5);
				int tThree_month_supply = rs.getInt(6);
				String Bigletter = tUnit.substring(0, 1);
				String theRest = tUnit.substring(1);
				Size tempSize = new Size(tSize, SizeUnits.valueOf(Bigletter.toUpperCase() + theRest));

				resultProduct = new Product(tBarcode, tDescription, tempSize, tShelf_life,
						tThree_month_supply);
			}

		}
		catch (SQLException e)
		{
			System.out.println("Error in getProductByBarcode");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				System.out.println("Error in execution");
				e.printStackTrace();
			}
		}

		ValueResult<Product> result = new ValueResult<Product>(true);
		result.setValue(resultProduct);
		return result;
	}

	@Override
	public Result add(Product product)
	{
		Result result = new Result();
		try
		{
			String query = "INSERT INTO products (description, barcode, size_measure, "
					+ "size_unit, shelf_life, three_month_supply) " + "VALUES (?, ?, ?, ?, ?, ?)";
			PreparedStatement stmt = db.getConnection().prepareStatement(query);

			stmt.setString(1, product.getDescription());
			stmt.setString(2, product.getBarcode().getCode());
			stmt.setFloat(3, product.getSize().getAmount());
			stmt.setString(4, product.getSize().getUnit().toString());
			stmt.setInt(5, product.getShelfLife());
			stmt.setInt(6, product.getThreeMonthSupply());

			if (stmt.executeUpdate() == 1)
			{
				result.setStatus(true);
			}
			else
			{
				result.setMessage("Error adding new product to database");
				return result;
			}
		}
		catch (SQLException e)
		{
			result.setMessage("Error adding new product to database: " + e.getMessage());
		}

		return result;
	}

	@Override
	public Result update(Product product)
	{
		Result result = new Result();
		try
		{
			String query = "UPDATE Product SET description, barcode, size_measure, "
					+ "size_unit, shelf_life, three_month_supply " + "values(?, ?, ?, ?, ?, ?)";

			PreparedStatement stmt = db.getConnection().prepareStatement(query);
			stmt.setString(1, product.getDescription());
			stmt.setString(2, product.getBarcode().getCode());
			stmt.setFloat(3, product.getSize().getAmount());
			stmt.setString(4, product.getSize().getUnit().toString());
			stmt.setInt(5, product.getShelfLife());
			stmt.setInt(6, product.getThreeMonthSupply());

			if (stmt.executeUpdate() == 1)
			{
				result.setStatus(true);
			}
			else
			{
				result.setMessage("Error updating product in database");
			}
		}
		catch (SQLException e)
		{
			result.setMessage("Error updating product in database: " + e.getMessage());
		}

		return result;
	}

	@Override
	public Result delete(Product product)
	{
		Result result = new Result();
		try
		{
			String query = "DELETE FROM Product WHERE barcode = ?";
			PreparedStatement stmt = db.getConnection().prepareStatement(query);
			stmt.setString(1, product.getBarcode().getCode());
			if (stmt.executeUpdate() == 1)
			{
				result.setStatus(true);
			}
			else
			{
				result.setMessage("Error deleting product from database");
			}
		}
		catch (SQLException e)
		{
			result.setMessage("Error deleting product from database: " + e.getMessage());
		}
		return result;
	}

	@Override
	public Result saveProducts(java.util.Set<Product> products)
	{
		return new Result(false, "Method not supported for this persistence type.");
	}

	@Override
	public Result saveProductsByContainer(Map<ProductContainer, Set<Product>> productsByContainer)
	{
		return new Result(true);
	}

	@Override
	public Result addToProductContainer(Product product, ProductContainer container) 
	{
		if(checkIfProdcutInTheContainer(product.getBarcode().getCode(),container.getId())){
			return new Result(false,"prodcut alreay in the container.");
		}
		Result result = new Result();
		String query = "INSERT INTO products_to_containers(barcode, container_id) VALUES (?, ?)";

		PreparedStatement stmt;
		try {
			stmt = db.getConnection().prepareStatement(query);

			stmt.setString(1, product.getBarcode().getCode());
			stmt.setInt(2, container.getId());

			if (stmt.executeUpdate() == 1)
			{
				result.setStatus(true);
			}
			else
			{
				result.setMessage("Error adding to products_to_containers to database");
				return result;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	private Boolean checkIfProdcutInTheContainer(String barcode, int container_id){
		String query = "SELECT barcode, container_id FROM products_to_containers";

		PreparedStatement stmt;
		ResultSet rs = null;
		try {
			stmt = db.getConnection().prepareStatement(query);

			rs = stmt.executeQuery();
			if (rs.next()){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}
}
